package J21;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class J21_BookDBDAO implements J21_BookDBService{
	
	// JDBC 접속정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@127.0.0.1/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 정의
	
	
	private static final String insertSQL
		= "INSERT INTO BOOK(ID,TITLE,PUBLISHER,AUTHOR) VALUES(ID.NEXTVAL,?,?,?)";
	private static final String select1SQL
		= "SELECT ID,TITLE FROM BOOK ORDER BY ID DESC";
	private static final String select2SQL
		= "SELECT * FROM BOOK WHERE ID = ?";
	private static final String updateSQL
		= "UPDATE BOOK SET TITLE = ?,PUBLISHER = ?,AUTHOR = ?, REGDATE = SYSDATE WHERE ID =?";
	private static final String deleteSQL
		= "DELETE FROM BOOK WHERE ID = ?";
	
	
	
	@Override
	public int insertBookDB(J21_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
			
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if( ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		return cnt;
	}

	@Override
	public String selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	// insert,update에 비해 추가됨!
		StringBuilder result = new StringBuilder();
//		String result = "";		// 나중에 ArrayList로 변경
		
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(select1SQL);
			rs = ps.executeQuery();
			
			// select문 실행 후 결과 처리
			while(rs.next()){
				for(int i=1;i <= 2;++i) // i <= 2에서 2는 컬럼갯수
//					result += rs.getString(i) + " ";
//				result += "\n";
				result.append(rs.getString(i))
					  .append( " " );
				result.append("\n");
				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){}
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
		return result.toString();
	}

	@Override
	public String selectBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder result = new StringBuilder();		
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(select2SQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i=1; i <= 5; ++i)
					result.append(rs.getString(i))
						  .append(" ");
				result.append("\n");
			}
		}catch(Exception ex){
		}finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){}
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
		return result.toString();
	}

	@Override
	public int updateBookDB(J21_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
				ps.setString(4, bvo.getId());
			
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if( ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		return cnt;
	}

	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);			
				cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if( ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		return cnt;
	}

}
